package com.neu.dao.ww;

import com.neu.dao.BaseDao;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class OrderInfoDao extends BaseDao {
    public int insertOrderInfo(List<List<String>> lists) {
        int row = 0;
        String sql = "insert into T_ORDER_INFO(I_ID, O_ID, G_ID, G_NAME, I_PRICE, I_NUM) values " +
                "(SEQ_ID.nextval,(select max(O_ID)from T_ORDER),?,?,?,?)";
        try {
            row = super.executeBatch(sql,lists);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return row;
    }

    public List<Map<String, Object>> findOrderInfoByDate(Object... objs) {
        List<Map<String, Object>> list = null;
        String sql = "select t_good.g_name,sum(t_order_info.i_num) nums,sum(t_order_info.i_price*t_order_info.i_num) prices" +
                "     from t_order,t_order_info,t_good,t_kind" +
                "     where t_order.o_id=t_order_info.o_id and t_good.g_id=t_order_info.g_id" +
                "     and t_good.k_id=t_kind.k_id" +
                "     and o_date=to_date(?,'yyyy-MM-dd') and k_name=?" +
                "     group by t_good.g_id,t_good.g_name";
        try {
            list = getQueryRunner().query(sql, new MapListHandler(), objs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    public List<Map<String, Object>> findOrderInfoByMonth(Object... objs) {
        List<Map<String, Object>> list = null;
        String sql = "select t_good.g_name,sum(t_order_info.i_num) nums,sum(t_order_info.i_price*t_order_info.i_num) prices" +
                "     from t_order,t_order_info,t_good,t_kind" +
                "     where t_order.o_id=t_order_info.o_id and t_good.g_id=t_order_info.g_id" +
                "     and t_good.k_id=t_kind.k_id" +
                "     and to_char(O_DATE,'MM')=? and k_name=?" +
                "     group by t_good.g_id,t_good.g_name";
        try {
            list = getQueryRunner().query(sql, new MapListHandler(), objs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<Map<String, Object>> findOrderInfoTen() {
        List<Map<String, Object>> list = null;
        String sql = "select name,nums,rownum from " +
                " (select t_good.g_name name,sum(t_order_info.i_num) nums," +
                " sum(t_order_info.i_price*t_order_info.i_num) prices" +
                " from t_order,t_order_info,t_good,t_kind" +
                " where t_order.o_id=t_order_info.o_id and t_good.g_id=t_order_info.g_id" +
                " and t_good.k_id=t_kind.k_id" +
                " group by t_good.g_id,t_good.g_name" +
                " order by nums desc) " +
                " where rownum <=10";
        try {
            list = getQueryRunner().query(sql, new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
